﻿<%@ WebHandler Language="C#" Class="GZBaseDataHandler" %>

using System;
using System.Web;
using Newtonsoft.Json;
using System.Linq;
using System.Web.SessionState;
using System.Collections.Generic;
using BusinessRule.DTO;
using System.Data;
using System.IO;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Collections;
using System.Web.SessionState;
using BusinessRule.Business;
using Camstar.WebClient.WebServicesProxy.InSiteWebServices;
using DatabaseAccess.DBUtility;
using System.Text.RegularExpressions;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;

//Description:工装Excel上传数据处理程序
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:zhangrj
//create Date:2022-02-25
public class GZBaseDataHandler : IHttpHandler, IRequiresSessionState
{
    uLMSContainerIBusiness m_ContainerObj = uMESBusinessFactory.instance.GetuLMSContainerObject();
    GZBaseDataBusiness gzBusiness = new GZBaseDataBusiness();
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();
    CurrentUserInfo m_UserInfo = new CurrentUserInfo();
    string fileBasePath = "/InSiteWebApplication/Temp2/GZBaseData/";
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            switch (action)
            {
                case "GetWorkCenterList": //获取工区数据
                    GetWorkCenterList(context);
                    break;
                case "UploadGZBaseExcel": //上传多工作薄文件
                    CreateDirectory(context);
                    UploadeGZBaseFile(context);
                    break;
                case "DownloadGZSheetFile"://下载多工作薄文件的工作薄
                    DownloadGZSheetFile(context);
                    break;
                case "UploadProductBomExcel"://上传BomExcel并预览数据
                    CreateDirectory(context);
                    UploadProductBomExcel(context);
                    break;
                case "UploadWorkflowExcel"://上传工艺规程Excel并预览数据
                    CreateDirectory(context);
                    UploadWorkflowExcel(context);
                    break;
                case "ViewProductBomExcel"://预览多工作薄中的产品BOM信息
                    ViewProductBomExcel(context);
                    break;
                case "ViewWorkflowExcel"://预览多工作薄中的工艺规程信息
                    ViewWorkflowExcel(context);
                    break;
                case "SaveProductBomDatas"://保存产品BOM上传数据
                    SaveProductBomData(context);
                    break;
                case "SaveWorkflowDatas"://保存工艺流程上传数据
                    SaveWorkflowDatas(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
    /// <summary>
    /// 初始化工艺规程上传页面数据
    /// </summary>
    /// <param name="context"></param>
    public void GetWorkCenterList(HttpContext context)
    {
        m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
        DataTable dtWorkCenter = commonBusiness.GetWorkCenterByFacoryID(m_UserInfo.FactoryID, false);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, DBTable = dtWorkCenter }));
    }

    /// <summary>
    /// 下载多工作薄文件中的工作薄
    /// </summary>
    /// <param name="context"></param>
    public void DownloadGZSheetFile(HttpContext context)
    {
        try
        {
            string fileName = context.Request.Form["fileName"].ToString();
            string sheetName = context.Request.Form["sheetName"].ToString();
            int fileIndex = int.Parse(context.Request.Form["fileIndex"].ToString());
            string fpath = context.Server.MapPath(fileBasePath + fileName);
            IWorkbook workbook = GetWorkbookObj(fpath);
            if (workbook == null)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析出错", Result = false }));
                return;
            }
            int len = workbook.NumberOfSheets;
            for (int i = 0; i < len; i++)
            {
                if (fileIndex == 0)
                {
                    if (workbook.NumberOfSheets > 1)
                    {
                        workbook.RemoveSheetAt(1);
                    }
                }
                else
                {
                    workbook.RemoveSheetAt(0);
                }
                if (fileIndex != 0)
                {
                    fileIndex--;
                }
            }
            string downfile = context.Server.MapPath(fileBasePath + sheetName + Path.GetExtension(fileName));
            FileStream fs = new FileStream(downfile, FileMode.Create);
            workbook.Write(fs);
            fs.Close();
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "下载成功", Result = true, ReturnData = sheetName + Path.GetExtension(fileName) }));
        }
        catch (Exception ex)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    /// <summary>
    /// 上传并预览多工作薄文件
    /// </summary>
    /// <param name="context"></param>
    public void UploadeGZBaseFile(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = Path.GetExtension(file.FileName);
            string fileName = Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件
            IWorkbook Workbook = GetWorkbookObj(fpath);
            if (Workbook == null)
            {
                DeleteFile(fpath);
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析出错！", Result = false }));
                return;
            }
            DataTable dt = new DataTable();
            dt.Columns.Add("sheetName");
            dt.Columns.Add("remark");
            dt.Columns.Add("sheetIndex");
            dt.Columns.Add("sheetIndex1");
            dt.Columns.Add("fileName");
            int sheetCount = Workbook.NumberOfSheets;
            if (sheetCount <= 1)
            {
                DeleteFile(fpath);
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "单个工作薄不能上传！", Result = false }));
                return;
            }
            for (int i = 0; i < sheetCount; i++)
            {
                ISheet sheet = Workbook.GetSheetAt(i);
                DataRow dr = dt.NewRow();
                dr["sheetIndex"] = i;
                dr["sheetIndex1"] = i;
                dr["sheetName"] = file.FileName.Replace(IsXls, "") + "-" + sheet.SheetName;
                dr["fileName"] = fileName;
                dt.Rows.Add(dr);
            }
            //DeleteFile(fpath);
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "预览成功", Result = true, DBTable = dt }));
        }
        catch (Exception e)
        {
            DeleteFile(fpath);
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = e.Message, Result = false }));
        }
    }

    /// <summary>
    /// 获取工作薄对象
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public IWorkbook GetWorkbookObj(string filePath)
    {
        FileStream fileStream = null;
        try
        {
            string IsXls = Path.GetExtension(filePath);
            //读取文件流
            fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            fileStream.Position = 0;
            IWorkbook Workbook = null;
            switch (IsXls)
            {
                case ".xlsx":
                    Workbook = new XSSFWorkbook(fileStream);
                    break;
                case ".xls":
                    Workbook = new HSSFWorkbook(fileStream);
                    break;
                default:
                    break;
            }
            fileStream.Close();//关闭文件流
            return Workbook;
        }
        catch (Exception e)
        {
            fileStream.Close();//关闭文件流
            return null;
        }
    }

    #region 保存BOM数据
    /// <summary>
    /// 保存上传预览的Bom数据
    /// </summary>
    /// <param name="context"></param>
    public void SaveProductBomData(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult();
        m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
        DataTable recordDt = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["recordDt"].ToString());
        if (m_UserInfo == null || recordDt.Rows.Count == 0)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "缓存过期" }));
            return;
        }
        //最顶层的件号信息
        DataRow drTop = recordDt.AsEnumerable().FirstOrDefault(p => !string.IsNullOrWhiteSpace(p["IsTopGz"].ToString()));
        if (drTop == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "未找到顶层件号" }));
            return;
        }


        //创建产品
        result = SaveProductData(recordDt);
        //创建BOM
        if (result.Result)
        {
            for (int i = 0; i < recordDt.Rows.Count; i++)
            {
                string faProductName = recordDt.Rows[i]["ProductName"].ToString();//假设每一行的零件号都为父零件
                string faProductRev = recordDt.Rows[i]["ProductRev"].ToString();
                string faProductDesc = recordDt.Rows[i]["ProductDesc"].ToString();
                string bomRev = recordDt.Rows[i]["BomRev"].ToString();

                List<DataRow> childDt = (from dr in recordDt.AsEnumerable()
                                         where Regex.Matches(dr["ProductName"].ToString().Replace(faProductName, ""), "-").Count == 1
                                         && dr["ProductName"].ToString().Replace(faProductName, "").Length > 0
                                         && dr["ProductName"].ToString().Replace(faProductName, "").ToCharArray()[0] == '-'
                                         select dr).ToList();
                if (childDt.Count >= 1) //如果该零件的子零件不为空，则创建BOM
                {
                    result = SaveBomData(faProductName, faProductRev, bomRev, drTop, childDt);
                    if (result.Result)//更新父零件
                    {
                        Dictionary<string, object> dic = new Dictionary<string, object>();
                        dic.Add("ProductName", faProductName);
                        dic.Add("ProductRev", faProductRev);
                        dic.Add("ProductDesc", string.IsNullOrWhiteSpace(faProductDesc) ? " " : faProductDesc);
                        dic.Add("ProductType", "工装件");
                        dic.Add("BomName", "Bom-" + faProductName);
                        dic.Add("BomRev", bomRev);
                        dic.Add("IsTopGz", recordDt.Rows[i]["IsTopGz"].ToString());//是否是工装最顶层的件号
                        dic.Add("Materialtrademark", recordDt.Rows[i]["Materialtrademark"].ToString());
                        dic.Add("MaterialSpecification", recordDt.Rows[i]["MaterialSpecification"].ToString());
                        dic.Add("MaterialCode", recordDt.Rows[i]["MaterialCode"].ToString());
                        dic.Add("UserName", m_UserInfo.APIUserName);
                        dic.Add("Password", m_UserInfo.APIPassword);
                        result = gzBusiness.UpdateProduct(dic);
                    }
                }
                if (!result.Result)
                {
                    break;
                }
            }
        }
        result.Message = result.Result ? "创建成功" : result.Message;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 创建或更新产品
    /// </summary>
    /// <param name="recordDt"></param>
    /// <returns></returns>
    private uMESProcessResult SaveProductData(DataTable recordDt)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        Dictionary<string, object> dic = new Dictionary<string, object>();
        foreach (DataRow item in recordDt.Rows)
        {
            dic = new Dictionary<string, object>();
            dic.Add("ProductName", item["ProductName"].ToString());
            dic.Add("ProductRev", item["ProductRev"].ToString());
            dic.Add("OldProductRev", (int.Parse(item["ProductRev"].ToString()) - 1).ToString());
            dic.Add("ProductDesc", string.IsNullOrWhiteSpace(item["ProductDesc"].ToString()) ? " " : item["ProductDesc"].ToString());
            dic.Add("ProductType", "工装件");
            dic.Add("IsTopGz", item["IsTopGz"].ToString());//是否是工装最顶层的件号
            dic.Add("Materialtrademark", item["Materialtrademark"].ToString());
            dic.Add("MaterialSpecification", item["MaterialSpecification"].ToString());
            dic.Add("MaterialCode", item["MaterialCode"].ToString());
            dic.Add("MaterialSize", item["MaterialSize"].ToString());
            dic.Add("TechnicianAndTime", item["TechnicianAndTime"].ToString());
            dic.Add("UserName", m_UserInfo.APIUserName);
            dic.Add("Password", m_UserInfo.APIPassword);
            DataTable dt = gzBusiness.GetRDOInfo("product", dic["ProductName"].ToString(), item["ProductRev"].ToString());
            result = dt.Rows.Count == 0 ? item["ProductRev"].ToString() == "1" ? gzBusiness.CreateProduct(dic) : gzBusiness.CreateProductRev(dic) : gzBusiness.UpdateProduct(dic);
            if (!result.Result)//调用api失败，则结束
            {
                break;
            }
        }

        return result;
    }

    /// <summary>
    /// 创建或更新Bom信息
    /// </summary>
    /// <param name="faProductName">父零件号</param>
    /// <param name="faProductRev">父零件版本</param>
    /// <param name="bomRev">BOM版本</param>
    ///  <param name="drTop">顶层件号信息</param>
    /// <param name="childDt">子零件列表</param>
    /// <returns></returns>
    private uMESProcessResult SaveBomData(string faProductName, string faProductRev, string bomRev, DataRow drTop, List<DataRow> childDt)
    {
        uMESProcessResult result = new uMESProcessResult();
        Dictionary<string, object> dic = new Dictionary<string, object>();
        dic.Add("BomName", "Bom-" + faProductName);
        dic.Add("BomRev", bomRev);
        dic.Add("OldBomRev", (int.Parse(bomRev) - 1).ToString());
        dic.Add("BomDesc", " ");
        dic.Add("faProductName", faProductName);
        dic.Add("faProductRev", faProductRev);
        DataTable bomProductDt = new DataTable();
        bomProductDt.Columns.Add("ProductName");
        bomProductDt.Columns.Add("ProductRev");
        bomProductDt.Columns.Add("QtyRequired");
        bomProductDt.Columns.Add("Qty2Required");
        bomProductDt.Columns.Add("TopProductName");
        bomProductDt.Columns.Add("TopProductRev");
        childDt.ForEach(p =>
        {
            DataRow dr = bomProductDt.NewRow();
            dr["ProductName"] = p["ProductName"];
            dr["ProductRev"] = p["ProductRev"];
            dr["QtyRequired"] = p["QtyRequired"];
            dr["Qty2Required"] = p["Qty2Required"];
            dr["TopProductName"] = drTop["ProductName"];
            dr["TopProductRev"] = drTop["ProductRev"];
            bomProductDt.Rows.Add(dr);
        });
        dic.Add("MaterialList", bomProductDt);
        dic.Add("UserName", m_UserInfo.APIUserName);
        dic.Add("Password", m_UserInfo.APIPassword);
        DataTable dt = gzBusiness.GetRDOInfo("bom", dic["BomName"].ToString(), bomRev);
        //没数据则创建
        result = dt.Rows.Count == 0 ? bomRev == "1" ? gzBusiness.CreateBom(dic) : gzBusiness.CreateBomRev(dic) : gzBusiness.UpdateBom(dic);
        return result;
    }

    #endregion

    #region 保存工艺规程
    /// <summary>
    /// 保存上传预览的工艺、工序数据
    /// </summary>
    /// <param name="context"></param>
    public void SaveWorkflowDatas(HttpContext context)
    {
        uMESProcessResult results = new uMESProcessResult();//返回信息对象
        ResultStatus res = new ResultStatus();//数据验证返回对象
        m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
        DataTable recordDt = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["recordDt"].ToString());
        if (m_UserInfo == null || recordDt.Rows.Count == 0)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "缓存过期" }));
            return;
        }
        string workCenterName = context.Request.Form["workCenterName"].ToString();//工区名
        string workcenterID = context.Request.Form["workcenterID"].ToString();//工区ID
        if (string.IsNullOrWhiteSpace(workCenterName))
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "未选择工区" }));
            return;
        }
        res = SaveWorkflowAndStep(recordDt, workCenterName);//保存工序和工艺数据
        if (!res.IsError)//创建工艺、工序成功后，更新工时
        {
            res = SaveWorkTime(recordDt);//更新工时
        }
        if (!res.IsError)//更新工时成功后，更新产品
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic.Add("ProductName", recordDt.Rows[0]["ProductName"].ToString());
            dic.Add("ProductRev", "1");
            dic.Add("ProductDesc", recordDt.Rows[0]["Description"].ToString());
            dic.Add("ProductType", "工装件");
            dic.Add("Materialtrademark", recordDt.Rows[0]["Materialtrademark"].ToString());
            dic.Add("MaterialSpecification", recordDt.Rows[0]["MaterialSpecification"].ToString());
            //if (!string.IsNullOrWhiteSpace(recordDt.Rows[0]["MaterialSize"].ToString()))
            //{
            //    dic.Add("MaterialSize", recordDt.Rows[0]["MaterialSize"].ToString());
            //}
            dic.Add("WorkflowName", recordDt.Rows[0]["WorkflowName"].ToString());
            dic.Add("WorkflowRev", recordDt.Rows[0]["WorkflowRev"].ToString());
            dic.Add("UserName", m_UserInfo.APIUserName);
            dic.Add("Password", m_UserInfo.APIPassword);
            uMESProcessResult pres = gzBusiness.UpdateProduct(dic);
            res.IsError = !pres.Result;
            res.Message = pres.Message;
        }
        results.Message = res.IsError ? res.Message : "保存成功";
        results.Result = !res.IsError;
        //if (!res.IsError)//保存基础信息成功后，开卡
        //{
        //    res = CreateContainerInfo(recordDt, workCenterName);//创建批次
        //    results.Message = res.IsError ? "工艺路线创建成功，批次创建失败，失败原因：" + res.Message : "保存成功";
        //    results.Result = !res.IsError;
        //}
        context.Response.Write(JsonConvert.SerializeObject(results));
    }
    /// <summary>
    /// 保存工艺和工序的数据
    /// </summary>
    /// <param name="recordDt"></param>
    /// <param name="workcenterID"></param>
    /// <returns></returns>
    private ResultStatus SaveWorkflowAndStep(DataTable recordDt, string workCenterName)
    {
        ResultStatus result = new ResultStatus();
        Dictionary<string, string> dic = new Dictionary<string, string>();
        string productName = recordDt.Rows[0]["ProductName"].ToString();
        string workflowName = recordDt.Rows[0]["WorkflowName"].ToString();
        string workflowRev = recordDt.Rows[0]["WorkflowRev"].ToString();
        string process_No = recordDt.Rows[0]["Process_No"].ToString();
        DataTable pdt = gzBusiness.GetRDOInfo("product", productName, "1");
        if (pdt.Rows.Count == 0)
        {
            result.IsError = true;
            result.Message = "该工艺规程没有零件数据";
            return result;
        }
        DataTable dtStep = new DataTable();
        dtStep.Columns.Add("StepName");
        dtStep.Columns.Add("ObjName");
        dtStep.Columns.Add("ObjRev");
        dtStep.Columns.Add("ObjType");
        dtStep.Columns.Add("Description");
        //创建工序
        foreach (DataRow item in recordDt.Rows)
        {
            dic.Clear();
            string specNo = item["SpecNo"].ToString().Trim().PadLeft(4, '0');
            string specName = productName + "-" + specNo + "-" + item["OpDesc"].ToString().Trim();
            dic.Add("SpecName", specName);//工序名称
            dic.Add("SpecRev", item["SpecRev"].ToString().Trim());//工序版本
            dic.Add("SpecNO", specNo.Trim());//工序号
            dic.Add("OPDesc", item["OpDesc"].ToString().Trim());//工序名
            dic.Add("Operation", "工装");
            dic.Add("Factory", m_UserInfo.Factory);
            dic.Add("SpecDesc", item["SpecDesc"].ToString().Replace("\n", "").Replace(" ", ""));//工序内容
            dic.Add("UserName", m_UserInfo.APIUserName.ToString());
            dic.Add("Password", m_UserInfo.APIPassword.ToString());
            dic.Add("SpecOldRev", (int.Parse(item["SpecRev"].ToString()) - 1).ToString());//旧版本
            dic.Add("ControlCode", item["ControlCode"].ToString().Trim());//控制码
            dic.Add("IsLastCheck", "0");//默认不是检验序
            dic.Add("Iskey", "0");//默认不是关键序
            DataTable dt = gzBusiness.GetRDOInfo("spec", specName, item["SpecRev"].ToString().Trim());
            if (dt.Rows.Count == 0)//如果不存在该版本工序，则添加
            {
                result = item["SpecRev"].ToString() == "1" ? commonBusiness.CreateSpec(dic) : commonBusiness.CreateNewRevSpec(dic);
                //result = commonBusiness.CreateSpec(dic);
                if (result.IsError)
                {
                    break;
                }
            }
            DataRow dr = dtStep.NewRow();
            dr["StepName"] = dic["SpecNO"] + "-" + dic["OPDesc"];
            dr["ObjName"] = dic["SpecName"];
            dr["ObjRev"] = dic["SpecRev"];
            dr["ObjType"] = "Spec";
            dr["Description"] = dic["SpecDesc"];
            dtStep.Rows.Add(dr);
        }
        if (!result.IsError)//创建工序成功后，创建工艺
        {
            dic.Clear();
            dic.Add("WorkflowName", workflowName);//工艺名
            dic.Add("Rev", workflowRev);//工艺版本
            dic.Add("Description", " ");
            dic.Add("ProductName", productName);//产品名
            dic.Add("ProductRev", "1");//产品版本
            dic.Add("Factory", m_UserInfo.Factory);
            dic.Add("UserName", m_UserInfo.APIUserName.ToString());
            dic.Add("Password", m_UserInfo.APIPassword.ToString());
            dic.Add("BjQty", recordDt.Rows[0]["PlannedQty2"].ToString());//备件数
            dic.Add("Qty", recordDt.Rows[0]["Qty"].ToString());//数量
            dic.Add("TaskNo", recordDt.Rows[0]["ContainerName"].ToString());//任务号
            dic.Add("MaterialSize", recordDt.Rows[0]["MaterialSize"].ToString());//毛料数量（从件号上存到工艺上）
            dic.Add("WorkCenterName", workCenterName);//工区
            if (workflowRev == "1")//如果版本是1，创建新对象，否则创建新版本
            {
                dic.Add("Process_No", process_No);//工艺规程编号
                dic.Add("Notes", " ");
                dic.Add("WorkFlowEditFlag", "Add");
                result = commonBusiness.SaveWorkflow(dic, dtStep);
            }
            else
            {
                dic.Add("ProcessNo", process_No);//工艺规程编号
                dic.Add("OldRev", (int.Parse(workflowRev) - 1).ToString());//旧版本
                result = commonBusiness.CreateWorkflowRev(dic, dtStep);
            }
        }
        DataRow drRecord = recordDt.Rows[0];
        if (!result.IsError && IsValidHuiQianData(drRecord))//创建工艺成功后且会签数据不全是空，保存工艺会签数据 add by zhangrj 20250107
        {
            DataTable dtWorkflow = commonBusiness.GetWorkflowInfoByNameAndRev(new Dictionary<string, string>() { { "WorkflowName", workflowName }, { "Rev", workflowRev } });//获取工艺信息
            StringBuilder hqSql = new StringBuilder();
            hqSql.AppendLine(@"INSERT INTO workflowsignatureinfo(id,workflowid,design,designtime,review,reviewtime,
                            huiqian1,huiqian1opinion,huiqian1time,huiqian2,huiqian2opinion,huiqian2time,huiqian3,huiqian3opinion,huiqian3time) VALUES(");
            hqSql.AppendFormat("'{0}',", Guid.NewGuid().ToString());
            hqSql.AppendFormat("'{0}',", dtWorkflow.Rows.Count > 0 ? dtWorkflow.Rows[0]["workflowid"].ToString() : string.Format("{0}&{1}", workflowName, workflowRev));
            hqSql.AppendFormat("'{0}',", drRecord["design"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["designtime"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["review"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["reviewtime"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["huiqian1"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["huiqian1opinion"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["huiqian1time"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["huiqian2"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["huiqian2opinion"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["huiqian2time"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["huiqian3"].ToString());
            hqSql.AppendFormat("'{0}',", drRecord["huiqian3opinion"].ToString());
            hqSql.AppendFormat("'{0}')", drRecord["huiqian3time"].ToString());
            if (DatabaseHelper.ExecuteSql(hqSql.ToString()) < 1)
            {
                result.IsError = true;
                result.Message = "工艺创建成功，但是会签数据保存失败。";
            }
        }
        return result;
    }
    
    /// <summary>
    /// 验证是否不为空的会签数据
    /// </summary>
    /// <param name="dr"></param>
    /// <returns></returns>
    private bool IsValidHuiQianData(DataRow dr)
    {
        if (!string.IsNullOrWhiteSpace(dr["design"].ToString()))
        {
            return true;
        }
        if (!string.IsNullOrWhiteSpace(dr["designtime"].ToString()))
        {
            return true;
        }
        if (!string.IsNullOrWhiteSpace(dr["review"].ToString()))
        {
            return true;
        }
        if (!string.IsNullOrWhiteSpace(dr["reviewtime"].ToString()))
        {
            return true;
        }
        bool res = false;
        for (int i = 1; i <= 3; i++)
        {
            if (!string.IsNullOrWhiteSpace(dr[string.Format("huiqian{0}",i)].ToString()))
            {
                res = true; break;
            }
            if (!string.IsNullOrWhiteSpace(dr[string.Format("huiqian{0}opinion", i)].ToString()))
            {
                res = true; break;
            }
            if (!string.IsNullOrWhiteSpace(dr[string.Format("huiqian{0}time", i)].ToString()))
            {
                res = true; break;
            }
        }
        return res;
    }
    
    /// <summary>
    /// 保存工序工时
    /// </summary>
    /// <param name="recordDt"></param>
    /// <returns></returns>
    private ResultStatus SaveWorkTime(DataTable recordDt)
    {
        ResultStatus result = new ResultStatus() { IsError = false };
        string workflowName = recordDt.Rows[0]["WorkflowName"].ToString();
        string workflowRev = recordDt.Rows[0]["WorkflowRev"].ToString();
        DataTable dt = gzBusiness.GetStepInfoByWorkflowNameRev(workflowName, workflowRev);
        foreach (DataRow item in recordDt.Rows)
        {
            DataRow[] row = dt.Select(string.Format("specno='{0}'", item["SpecNo"].ToString().PadLeft(4, '0')));
            if (row.Length > 0)
            {
                string sql = @"UPDATE stepschedulingdetail sd SET sd.durationperunit = '{0}' WHERE sd.stepid = '{1}'";
                sql = string.Format(sql, item["UnitTime"].ToString(), row[0]["workflowstepid"].ToString());
                result.IsError = DatabaseHelper.ExecuteSql(sql) <= 0;
                result.Message = result.IsError ? string.Format("更新工序（{0}）的工时出错", item["OpDesc"].ToString()) : "";
            }
            if (result.IsError)
            {
                break;
            }
        }
        return result;
    }

    #endregion

    #region 创建批次

    /// <summary>
    /// 创建批次
    /// </summary>
    /// <param name="recordDt">开卡的工艺工序等信息</param>
    /// <returns></returns>
    private ResultStatus CreateContainerInfo(DataTable recordDt, string workCenterName)
    {
        DataRow row = recordDt.Rows[0];
        string productName = row["ProductName"].ToString();//产品号
        string workflowName = row["WorkflowName"].ToString();//工艺
        string workflowRev = row["WorkflowRev"].ToString();//工艺版本
        string beginStep = row["SpecNo"].ToString().PadLeft(4, '0') + "-" + row["OPDesc"].ToString();//开始工序
        string containerName = row["ContainerName"].ToString();//批次号
        string qty = row["Qty"].ToString();//批次数量
        string plannedQty2 = row["PlannedQty2"].ToString();//备件
        string materialDate = row["MaterialDate"].ToString();//到料时间
        //填充批次信息
        ContainerStartInfo containerBaseInfo = new ContainerStartInfo();
        containerBaseInfo.ContainerName = productName + "/" + containerName;
        containerBaseInfo.YpContainerNo = "";
        containerBaseInfo.YpHeatNo = "";
        containerBaseInfo.YpContainerCardNo = "";
        containerBaseInfo.Qty = qty;
        containerBaseInfo.MfgOrder = "";
        containerBaseInfo.MonthPlan = "";//月度计划
        containerBaseInfo.IsSelfMfg = "1";//默认为是
        FillContainerDefaultInfo(ref containerBaseInfo);
        containerBaseInfo.TogetherFlag = productName + "/" + containerName;
        containerBaseInfo.Factory = m_UserInfo.Factory;
        containerBaseInfo.CheckTechnique = "";
        containerBaseInfo.UOM = "件";//默认单位为件
        containerBaseInfo.WorkCenter = workCenterName;
        containerBaseInfo.Team = "";
        containerBaseInfo.StartEmployee = m_UserInfo.EmployeeName;
        containerBaseInfo.BeginStep = beginStep;
        containerBaseInfo.ProductName = productName;
        containerBaseInfo.ProductRevision = "1";
        containerBaseInfo.ProductuseROR = false;
        containerBaseInfo.MfgWorkflowName = workflowName;
        containerBaseInfo.MfgWorkflowRevision = workflowRev;
        containerBaseInfo.MfgWorkflowuseROR = false;
        containerBaseInfo.WorkflowName = workflowName;//暂时取工艺名
        containerBaseInfo.WorkflowRevision = workflowRev;//
        containerBaseInfo.WorkflowuseROR = false;//
        containerBaseInfo.Comment = "";
        containerBaseInfo.PlannedStartDate = "";
        containerBaseInfo.PlannedCompletionDate = "";
        containerBaseInfo.ContainerType = "2";
        containerBaseInfo.MilitaryType = "";
        containerBaseInfo.YpContainerType = "";
        containerBaseInfo.PlannedQty2 = plannedQty2;//备件
        //containerBaseInfo.MaterialRequirementDate = materialDate;//存入领料日期（导入模板中最后一行最后一列的时间）
        ServerHostInfo serverInfo = new ServerHostInfo();
        serverInfo.UserName = m_UserInfo.APIUserName;
        serverInfo.Password = m_UserInfo.APIPassword;
        containerBaseInfo.ServerHostInfo = serverInfo;
        containerBaseInfo.QualificationQty = "1";
        //机型
        containerBaseInfo.MfgBatch = "";
        //containerBaseInfo.ProductFamilyList = "";
        //扩展属性
        containerBaseInfo.AttributeList = AddContainerAttribute(containerBaseInfo, row);
        //开卡
        ResultStatus result = m_ContainerObj.StartContainer(containerBaseInfo);
        //modify:wangjh 20230324 取消开卡成功的判断，工艺升版后,批次已存在，也得存储以下数据
        //if (!result.IsError) //开卡成功，
        //{
        gzBusiness.SaveConStepSomeInfo(containerBaseInfo.ContainerName.ToString(), recordDt, m_UserInfo);
        //bool isNotUpdate = true;//是否未更新containercurrentinfo表
        //string sql = string.Format("UPDATE containercurrentinfo c SET c.materialdate = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS') WHERE c.containername = '{1}'", materialDate, containerBaseInfo.ContainerName.ToString());
        //while (isNotUpdate)
        //{
        //    isNotUpdate = DatabaseHelper.ExecuteSql(sql) > 0 ? false : true;
        //}
        //}
        return result;
    }
    /// <summary>
    /// 填充批次默认信息
    /// </summary>
    /// <param name="containerBaseInfo"></param>
    /// <param name="factoryId"></param>
    private void FillContainerDefaultInfo(ref ContainerStartInfo containerBaseInfo)
    {
        DataTable levelAttributes = commonBusiness.GetFatoryAttributes(m_UserInfo.FactoryID);
        DataRow[] datarows = levelAttributes.Select("USERATTRIBUTENAME='默认所有者'");
        string strOwner = "";
        if (datarows.Length > 0)
        {
            strOwner = datarows[0]["ATTRIBUTEVALUE"].ToString();
        }
        datarows = levelAttributes.Select("USERATTRIBUTENAME='默认层级'");
        string strLevel = "";
        if (datarows.Length > 0)
        {
            strLevel = datarows[0]["ATTRIBUTEVALUE"].ToString();
        }
        string strStart = "";
        if (string.IsNullOrWhiteSpace(containerBaseInfo.MfgOrder.ToString()))
        {
            datarows = levelAttributes.Select("USERATTRIBUTENAME='无订单开始原因'");
        }
        else
        {
            datarows = levelAttributes.Select("USERATTRIBUTENAME='订单开始原因'");
        }
        if (datarows.Length > 0)
        {
            strStart = datarows[0]["ATTRIBUTEVALUE"].ToString();
        }

        containerBaseInfo.StartReason = strStart;
        containerBaseInfo.Level = strLevel;
        containerBaseInfo.Owner = strOwner;
        string test = containerBaseInfo.IsSelfMfg.ToString();
        if (containerBaseInfo.IsSelfMfg.ToString() == "True")
        {
            datarows = levelAttributes.Select("USERATTRIBUTENAME='自投料流程'");
        }
        else
        {
            datarows = levelAttributes.Select("USERATTRIBUTENAME='领料流程'");
        }
        if (datarows.Length > 0)
        {
            string[] strWorkflows = datarows[0]["ATTRIBUTEVALUE"].ToString().Split('：');
            containerBaseInfo.WorkflowName = strWorkflows.First();
            containerBaseInfo.WorkflowRevision = strWorkflows.Last();
            containerBaseInfo.WorkflowuseROR = false;
        }
    }
    /// <summary>
    /// 添加流水卡扩展属性
    /// </summary>
    /// <param name="containerInfo"></param>
    /// <param name="row"></param>
    /// <returns></returns>
    private DataTable AddContainerAttribute(ContainerStartInfo containerInfo, DataRow dr)
    {
        DataTable attributeDt = new DataTable();
        attributeDt.Columns.Add("ContainerName");
        attributeDt.Columns.Add("Level");
        attributeDt.Columns.Add("Name");
        attributeDt.Columns.Add("Value");
        DataRow row = attributeDt.NewRow();
        SetAttributeRow(ref row, containerInfo, "CheckTechnique", "");
        attributeDt.Rows.Add(row);
        //if (!string.IsNullOrWhiteSpace(containerInfo.MonthPlan.ToString()))
        //{
        //    row = attributeDt.NewRow();
        //    SetAttributeRow(ref row, containerInfo, "monthPlanName", containerInfo.MonthPlan.ToString());
        //}
        row = attributeDt.NewRow();
        SetAttributeRow(ref row, containerInfo, "BeginStep", containerInfo.BeginStep.ToString());
        attributeDt.Rows.Add(row);
        //if (!string.IsNullOrWhiteSpace(dr["TechnicianAndTime"].ToString()))
        //{
        //    row = attributeDt.NewRow();
        //    SetAttributeRow(ref row, containerInfo, "工艺编制信息", dr["TechnicianAndTime"].ToString());
        //    attributeDt.Rows.Add(row);
        //}
        //if (!string.IsNullOrWhiteSpace(dr["ProofreaderAndTime"].ToString()))
        //{
        //    row = attributeDt.NewRow();
        //    SetAttributeRow(ref row, containerInfo, "工艺校对信息", dr["ProofreaderAndTime"].ToString());
        //    attributeDt.Rows.Add(row);
        //}

        if (containerInfo.IsSelfMfg.ToString() == "False")
        {
            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "材料牌号", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "实发数/顺序号", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "熔炉号母", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "检验编号", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "库房保管", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "代料牌号", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "代料规格", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "代料单号", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "平行作业单号", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "平行作业工序", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "器材检验", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "顾客代表", "");
            attributeDt.Rows.Add(row);

            row = attributeDt.NewRow();
            SetAttributeRow(ref row, containerInfo, "不合格审理单号", "");
            attributeDt.Rows.Add(row);
        }
        return attributeDt;
    }
    /// <summary>
    /// 设置属性行
    /// </summary>
    /// <param name="row"></param>
    /// <param name="containerInfo"></param>
    /// <param name="attributeName"></param>
    /// <param name="attributeValue"></param>
    private void SetAttributeRow(ref DataRow row, ContainerStartInfo containerInfo, string attributeName, string attributeValue)
    {
        row["ContainerName"] = containerInfo.ContainerName;
        row["Level"] = containerInfo.Level;
        row["Name"] = attributeName;
        row["Value"] = attributeValue;
    }

    #endregion

    #region 上传预览
    /// <summary>
    /// 上传产品Bom文件并预览
    /// </summary>
    /// <param name="context"></param>
    public void UploadProductBomExcel(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = Path.GetExtension(file.FileName);
            string fileName = Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除

            IWorkbook Workbook = GetWorkbookObj(fpath);
            if (Workbook == null)
            {
                DeleteFile(fpath);
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析出错", Result = false }));
                return;
            }
            ISheet sheet = Workbook.GetSheetAt(0);
            uMESProcessResult result = GetProductBOMData(sheet, IsXls);//获取解析后的数据
            if (!result.Result)
            {
                DeleteFile(fpath);
            }
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    /// <summary>
    /// 预览产品Bom文件
    /// </summary>
    /// <param name="context"></param>
    public void ViewProductBomExcel(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            string fileName = context.Request.Form["fileName"].ToString();
            fpath = context.Server.MapPath(fileBasePath + fileName);
            IWorkbook Workbook = GetWorkbookObj(fpath);
            if (Workbook == null)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析出错", Result = false }));
                return;
            }
            ISheet sheet = Workbook.GetSheetAt(0);//处理BOM只需第一个工作薄
            uMESProcessResult result = GetProductBOMData(sheet, Path.GetExtension(fileName));//获取解析后的数据
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    /// <summary>
    /// 获取产品BOM解析数据
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="xlsType"></param>
    /// <returns></returns>
    public uMESProcessResult GetProductBOMData(ISheet sheet, string xlsType)
    {
        uMESProcessResult result = new uMESProcessResult();
        DataTable recordDt = new DataTable();
        recordDt.Columns.Add("ProductName");
        recordDt.Columns.Add("ProductDesc");
        recordDt.Columns.Add("Materialtrademark");
        recordDt.Columns.Add("MaterialSpecification");
        recordDt.Columns.Add("QtyRequired");
        recordDt.Columns.Add("MaterialCode");
        recordDt.Columns.Add("ProductRev");
        recordDt.Columns.Add("BomRev");
        recordDt.Columns.Add("MaterialSize");//毛料数量
        recordDt.Columns.Add("Qty2Required");//重量
        recordDt.Columns.Add("IsTopGz");//是否工装最顶层件号
        recordDt.Columns.Add("TechnicianAndTime");//工艺员和时间
        int i = 1;
        bool isValid = true, isNewRev = false;//验证，是否最新版本
        string errorMsg = "", fProductRev = "";//父零件版本（子零件同步该版本）
        IRow row = null;
        IEnumerator rows = sheet.GetRowEnumerator();
        rows.MoveNext();//跳过表头
        while (rows.MoveNext())
        {
            switch (xlsType)
            {
                case ".xlsx":
                    row = (XSSFRow)rows.Current;
                    break;
                case ".xls":
                    row = (HSSFRow)rows.Current;
                    break;
            }
            if (row.Cells.Count == 0)//如果单元格为空，结束
            {
                break;
            }
            if (GetCellValue(row.GetCell(0)).Contains("工"))//如果改行第一列为工艺员，解析数据，并结束
            {
                //string technicianAndTime = GetCellValue(row.GetCell(6)) + "&" + GetCellValue(row.GetCell(1));
                //recordDt.Rows[0]["TechnicianAndTime"] = technicianAndTime.Trim('&');//工艺员和时间
                break;
            }
            if (i == 1)//处理表头数据（处理第一行数据）
            {
                DataRow dr = recordDt.NewRow();
                dr["ProductName"] = GetCellValue(row.GetCell(1));
                if (string.IsNullOrWhiteSpace(dr["ProductName"].ToString()))
                {
                    errorMsg = "图号为空，请检查文件数据";
                    isValid = false;
                    break;
                }
                string sqlJT = string.Format("SELECT jt.t_code FROM jbst_sync_mes jt WHERE jt.productname = '{0}'", dr["ProductName"].ToString());
                DataTable dtJT = DatabaseHelper.Query_ReturnDatTable(sqlJT);
                if (dtJT.Rows.Count > 0)
                {
                    dr["MaterialCode"] = dtJT.Rows[0]["t_code"].ToString();
                }
                DataTable dt = gzBusiness.GetRDOInfo("product", dr["ProductName"].ToString(), "");
                DataTable dt1 = gzBusiness.GetRDOInfo("bom", "Bom-" + dr["ProductName"].ToString(), "");
                dr["ProductRev"] = dt.Rows.Count == 0 ? "1" : (int.Parse(dt.Rows[0]["productrevision"].ToString()) + 1).ToString();
                dr["BomRev"] = dt1.Rows.Count == 0 ? "1" : (int.Parse(dt1.Rows[0]["bomrevision"].ToString()) + 1).ToString();
                if (dt.Rows.Count > 0)
                {
                    dr["ProductDesc"] = dt.Rows[0]["description"].ToString();
                    isNewRev = true;
                }
                dr["IsTopGz"] = "1";//表示是最顶层件号
                recordDt.Rows.Add(dr);
            }
            else if (i >= 4)//处理表格数据（从第4行开始）
            {
                DataRow dr = recordDt.NewRow();
                dr["ProductName"] = GetCellValue(row.GetCell(1));
                dr["Materialtrademark"] = GetCellValue(row.GetCell(3));
                dr["MaterialSpecification"] = GetCellValue(row.GetCell(4));
                dr["QtyRequired"] = GetCellValue(row.GetCell(2));
                dr["MaterialSize"] = GetCellValue(row.GetCell(5));
                dr["MaterialCode"] = GetCellValue(row.GetCell(8));
                dr["Qty2Required"] = GetProductWeight(GetCellValue(row.GetCell(9)));
                if (IsValidRow(dr, true))//有效的行数据才添加
                {
                    if (string.IsNullOrWhiteSpace(dr["ProductName"].ToString()))
                    {
                        errorMsg = "第" + (i + 1) + "行的零件号为空";
                        isValid = false;
                        break;
                    }
                    if (string.IsNullOrWhiteSpace(dr["QtyRequired"].ToString()))
                    {
                        errorMsg = "第" + (i + 1) + "行的数量为空";
                        isValid = false;
                        break;
                    }
                    DataTable dt = gzBusiness.GetRDOInfo("product", dr["ProductName"].ToString(), "");
                    DataTable dt1 = gzBusiness.GetRDOInfo("bom", "Bom-" + dr["ProductName"].ToString(), "");
                    dr["ProductRev"] = dt.Rows.Count == 0 ? "1" : (int.Parse(dt.Rows[0]["productrevision"].ToString()) + 1).ToString();
                    dr["BomRev"] = dt1.Rows.Count == 0 ? "1" : (int.Parse(dt1.Rows[0]["bomrevision"].ToString()) + 1).ToString();
                    if (dr["ProductRev"].ToString() != "1")
                    {
                        dr["ProductDesc"] = dt.Rows[0]["description"].ToString();
                    }
                    recordDt.Rows.Add(dr);
                }
            }
            i++;
        }
        result.Result = isValid;
        result.DBTable = recordDt;
        result.Message = errorMsg;
        result.ReturnData = isNewRev;
        return result;
    }

    /// <summary>
    /// 上传工艺规程文件并预览
    /// </summary>
    /// <param name="context"></param>
    public void UploadWorkflowExcel(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = Path.GetExtension(file.FileName);
            string fileName = Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除

            IWorkbook Workbook = GetWorkbookObj(fpath);
            if (Workbook == null)
            {
                DeleteFile(fpath);
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析出错", Result = false }));
                return;
            }
            ISheet sheet = Workbook.GetSheetAt(0);
            uMESProcessResult result = GetWorkflowExcelData(sheet, IsXls);
            if (!result.Result)
            {
                DeleteFile(fpath);
            }
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    /// <summary>
    /// 预览工艺规程excel数据
    /// </summary>
    public void ViewWorkflowExcel(HttpContext context)
    {
        try
        {
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            string fileName = context.Request.Form["fileName"].ToString();
            int fileIndex = int.Parse(context.Request.Form["fileIndex"].ToString());
            string fpath = context.Server.MapPath(fileBasePath + fileName);
            IWorkbook Workbook = GetWorkbookObj(fpath);
            if (Workbook == null)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析出错", Result = false }));
                return;
            }
            ISheet sheet = Workbook.GetSheetAt(fileIndex);//处理文件中第index个工作薄
            uMESProcessResult result = GetWorkflowExcelData(sheet, Path.GetExtension(fileName));//获取解析后的数据
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }

    /// <summary>
    /// 获取解析后的工艺规程数据
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="xlsType"></param>
    /// <returns></returns>
    public uMESProcessResult GetWorkflowExcelData(ISheet sheet, string xlsType)
    {
        DataTable recordDt = new DataTable();
        recordDt.Columns.Add("ProductName");
        recordDt.Columns.Add("Description");
        recordDt.Columns.Add("Materialtrademark");//材料牌号
        recordDt.Columns.Add("MaterialSpecification");//材料规格
        recordDt.Columns.Add("MaterialSize");//毛料数量
        recordDt.Columns.Add("WorkflowName");
        recordDt.Columns.Add("Process_No");
        recordDt.Columns.Add("SpecNo");
        recordDt.Columns.Add("OpDesc");
        recordDt.Columns.Add("SpecDesc");
        recordDt.Columns.Add("UnitTime");//单件工时
        recordDt.Columns.Add("WorkflowRev");
        recordDt.Columns.Add("SpecRev");
        recordDt.Columns.Add("ContainerName");
        recordDt.Columns.Add("Qty");//数量
        recordDt.Columns.Add("PlannedQty2");//备件
        //recordDt.Columns.Add("TechnicianAndTime");//工艺员和时间
        //recordDt.Columns.Add("ProofreaderAndTime");//校对员和时间
        recordDt.Columns.Add("MaterialDate");//批次到料时间
        recordDt.Columns.Add("Notes");//工序备注
        recordDt.Columns.Add("ControlCode");//工序控制码（专检序）
        /*工艺新增会签字段 add by zhangrj 20250107*/
        recordDt.Columns.Add("huiqian1");//会签1
        recordDt.Columns.Add("huiqian1opinion");//会签1意见
        recordDt.Columns.Add("huiqian1time");//会签1时间
        recordDt.Columns.Add("huiqian2");//会签2
        recordDt.Columns.Add("huiqian2opinion");//会签2意见
        recordDt.Columns.Add("huiqian2time");//会签2时间
        recordDt.Columns.Add("huiqian3");//会签3
        recordDt.Columns.Add("huiqian3opinion");//会签3意见
        recordDt.Columns.Add("huiqian3time");//会签3时间
        recordDt.Columns.Add("design");//工艺员
        recordDt.Columns.Add("designtime");//工艺员编制时间
        recordDt.Columns.Add("review");//审核员
        recordDt.Columns.Add("reviewtime");//审核时间

        int i = 1;//行数计数，
        int hqCount = 1;//会签数
        string workflowId = "";//最新版本工艺ID
        string contaienrName = "", productName = "", productDesc = "", plannedQty2 = "", //批次号，产品图号，产品名称,备件
            workflowName = "", qty = "", materialtrademark = "", //工艺规程名，批次数量，材料牌号
            materialSpecification = "", materialSize = "", process_no = "";//材料规格,毛料数量,工艺规程编号
        bool isValid = true, isNewRev = false;//是否有效（判断数据有效性），是否新版本（判断工艺、工序版本）
        string errorMsg = "", workflowRev = "", specRev = "";//错误信息，工艺规程版本，工序版本
        IEnumerator rows = sheet.GetRowEnumerator();
        IRow row = null;
        while (rows.MoveNext())
        {
            switch (xlsType)
            {
                case ".xlsx":
                    row = (XSSFRow)rows.Current;
                    break;
                case ".xls":
                    row = (HSSFRow)rows.Current;
                    break;
            }
            if (row.Cells.Count == 0)//如果单元格为空,结束
            {
                break;
            }
            if (i < 6) //如果6行前，读取工艺信息
            {
                switch (i)
                {
                    case 2: //第二行
                        contaienrName = GetCellValue(row.GetCell(1));//获取批次号
                        if (string.IsNullOrWhiteSpace(contaienrName))
                        {
                            errorMsg = "任务号为空，请检查文件数据";
                            isValid = false;
                            break;
                        }
                        process_no = GetCellValue(row.GetCell(6));//获取标准工艺编号
                        break;
                    case 3: //第三行
                        productName = GetCellValue(row.GetCell(1));
                        if (string.IsNullOrWhiteSpace(productName))
                        {
                            errorMsg = "图号为空，请检查文件数据";
                            isValid = false;
                            break;
                        }
                        productDesc = GetCellValue(row.GetCell(14));//产品描述
                        if (string.IsNullOrWhiteSpace(productDesc))
                        {
                            errorMsg = "名称为空，请检查文件数据";
                            isValid = false;
                            break;
                        }
                        plannedQty2 = GetCellValue(row.GetCell(9));//备件
                        plannedQty2 = Regex.Replace(plannedQty2, @"[^0-9]+", "");
                        //if (string.IsNullOrWhiteSpace(orginalQty2))
                        //{
                        //    errorMsg = "备件为空，请检查文件数据";
                        //    isValid = false;
                        //    break;
                        //}
                        qty = GetCellValue(row.GetCell(5));//数量
                        qty = Regex.Replace(qty, @"[^0-9]+", "");
                        if (string.IsNullOrWhiteSpace(qty))
                        {
                            errorMsg = "数量为空，请检查文件数据";
                            isValid = false;
                            break;
                        }
                        workflowName = "WF-" + productName;
                        DataTable dtWorkflow = gzBusiness.GetRDOInfo("workflow", workflowName, "");
                        if (dtWorkflow.Rows.Count == 0)//没有该工艺规程
                        {
                            workflowRev = "1";
                        }
                        else
                        {
                            workflowId = dtWorkflow.Rows[0]["workflowid"].ToString();//最新工艺id
                            workflowRev = (int.Parse(dtWorkflow.Rows[0]["workflowrevision"].ToString()) + 1).ToString();
                            isNewRev = true;
                        }
                        break;
                    case 4: //第四行
                        materialtrademark = GetCellValue(row.GetCell(1));
                        materialSpecification = GetCellValue(row.GetCell(5));
                        materialSize = GetCellValue(row.GetCell(7));
                        materialSize = Regex.Replace(materialSize, @"[^0-9]+", "");
                        break;
                    default:
                        break;
                }
            }
            else //从第六行开始，读取工序信息
            {
                string specNo = GetCellValue(row.GetCell(0));//获取第一列数据
                //if (specNo.Replace(" ", "").Contains("索引号") || specNo.Replace(" ", "").Contains("更改人"))
                //{
                //    continue;
                //}
                if (GetCellValue(row.GetCell(0)).Equals("会签")) //模板更改：edit by zhangrj 20250107
                {
                    if (hqCount > 3)
                    {
                        errorMsg = "会签行数超过三行";
                        isValid = false;
                        break;
                    }
                    string huiqians = GetCellValue(row.GetCell(14));
                    if (!string.IsNullOrWhiteSpace(huiqians))
                    {
                        string[] huiqian = huiqians.Split('/');//会签人和时间，以 / 隔开，第一个是会签人，第二个是会签时间
                        if (huiqian.Length != 2)
                        {
                            errorMsg = "会签人格式不正确（正确格式：会签人/会签时间）";
                            isValid = false;
                            break;
                        }
                        recordDt.Rows[0][string.Format("huiqian{0}", hqCount)] = huiqian[0];
                        recordDt.Rows[0][string.Format("huiqian{0}time", hqCount)] = huiqian[1];
                    }
                    string huiqianopinion = GetCellValue(row.GetCell(2));//会签意见
                    if (!string.IsNullOrWhiteSpace(huiqianopinion))
                    {
                        recordDt.Rows[0][string.Format("huiqian{0}opinion", hqCount)] = huiqianopinion;
                    }
                    hqCount++;
                    continue;
                }
                if (GetCellValue(row.GetCell(0)).Equals("工艺员"))//第一列包含工艺员，获取数据并结束循环
                {
                    //string technicianAndTime = GetCellValue(row.GetCell(2));
                    //string proofreaderAndTime = GetCellValue(row.GetCell(6));
                    //recordDt.Rows[0]["TechnicianAndTime"] = technicianAndTime.Trim('&');
                    //recordDt.Rows[0]["ProofreaderAndTime"] = proofreaderAndTime.Trim('&');

                    string techs = GetCellValue(row.GetCell(2));
                    if (!string.IsNullOrWhiteSpace(techs))
                    {
                        string[] tech = techs.Split('/'); //工艺员和时间，以 / 隔开，第一个是工艺员，第二个是编制时间
                        if (tech.Length != 2)
                        {
                            errorMsg = "工艺员格式不正确（正确格式：人员/时间）";
                            isValid = false;
                            break;
                        }
                        recordDt.Rows[0]["design"] = tech[0];
                        recordDt.Rows[0]["designtime"] = tech[1];
                    }
                    string auds = GetCellValue(row.GetCell(6));
                    if (!string.IsNullOrWhiteSpace(auds))
                    {
                        string[] aud = auds.Split('/');//审核员和时间，以 / 隔开，第一个是审核员，第二个是审核时间
                        if (aud.Length != 2)
                        {
                            errorMsg = "审核格式不正确（正确格式：人员/时间）";
                            isValid = false;
                            break;
                        }
                        recordDt.Rows[0]["review"] = aud[0];
                        recordDt.Rows[0]["reviewtime"] = aud[1];
                    }
                    break;
                }
                specNo = string.IsNullOrWhiteSpace(specNo) ? "" : specNo.PadLeft(4, '0');
                DataRow dr = recordDt.NewRow();
                dr["SpecNo"] = specNo;
                dr["OpDesc"] = GetCellValue(row.GetCell(1));
                dr["SpecDesc"] = GetCellValue(row.GetCell(2));
                dr["UnitTime"] = GetCellValue(row.GetCell(9));
                dr["Notes"] = GetCellValue(row.GetCell(17));
                dr["ControlCode"] = GetCellValue(row.GetCell(15));
                if (IsValidRow(dr, false))//有效的行数据才添加
                {
                    if (string.IsNullOrWhiteSpace(dr["SpecNo"].ToString()))
                    {
                        errorMsg = "第" + i + "行的序号为空";
                        isValid = false;
                        break;
                    }
                    if (string.IsNullOrWhiteSpace(dr["OpDesc"].ToString()))
                    {
                        errorMsg = "第" + i + "行的序称为空";
                        isValid = false;
                        break;
                    }
                    if (dr["OpDesc"].ToString() == "虚拟序")
                    {
                        dr["ControlCode"] = "LY07";
                    }
                    else if (string.IsNullOrWhiteSpace(dr["ControlCode"].ToString()))//如果没有填写专检序
                    {
                        dr["ControlCode"] = "LY08";//设置为报工后自动检验
                    }
                    else
                    {
                        //如果填写了专检序，则不存控制码
                        dr["ControlCode"] = "";
                    }
                    dr["ProductName"] = productName;
                    dr["Description"] = productDesc;
                    dr["WorkflowName"] = workflowName;
                    dr["WorkflowRev"] = workflowRev;
                    dr["Materialtrademark"] = materialtrademark;
                    dr["MaterialSpecification"] = materialSpecification;
                    dr["MaterialSize"] = materialSize;
                    dr["ContainerName"] = contaienrName;
                    dr["Qty"] = qty;
                    dr["PlannedQty2"] = plannedQty2;
                    dr["Process_No"] = process_no;
                    string specName = productName + "-" + specNo + "-" + dr["OpDesc"].ToString();
                    DataTable dtSpec = gzBusiness.GetRDOInfo("spec", specName, "");
                    if (dtSpec.Rows.Count == 0)//没有该工序
                    {
                        specRev = "1";
                    }
                    else
                    {
                        specRev = (int.Parse(dtSpec.Rows[0]["specrevision"].ToString()) + 1).ToString();
                        isNewRev = true;
                    }
                    dr["SpecRev"] = specRev;//工序版本
                    recordDt.Rows.Add(dr);
                }
            }
            i++;
        }
        //        if (isValid && !string.IsNullOrWhiteSpace(workflowId))//处理完数据成功后，检查最新工艺是否开卡
        //        {
        //            string sql = string.Format(@"SELECT cc.containerid FROM containercurrentinfo cc 
        //                             WHERE cc.workflowid='{0}'", workflowId);
        //            DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        //            if (dt.Rows.Count == 0)//没有则开卡
        //            {
        //                DataRow dr = recordDt.Rows[0];
        //                dr["WorkflowRev"] = int.Parse(dr["WorkflowRev"].ToString()) - 1;
        //                ResultStatus rs = CreateContainerInfo(dr);
        //                errorMsg = rs.Message;
        //                isValid = !rs.IsError;
        //            }
        //        }
        if (!isValid)
        {
            return new uMESProcessResult() { Message = errorMsg, Result = false };
        }
        ////判断最后一个序是不是虚拟序，如果是，则将倒数第二个序的控制码置为空，否则就将其控制码置为空
        //if (recordDt.Rows[recordDt.Rows.Count - 1]["OpDesc"].ToString() == "虚拟序")
        //{
        //    recordDt.Rows[recordDt.Rows.Count - 2]["ControlCode"] = "";
        //}
        //else
        //{
        //    recordDt.Rows[recordDt.Rows.Count - 1]["ControlCode"] = "";
        //}
        return new uMESProcessResult() { Message = "获取成功", Result = true, DBTable = recordDt, ReturnData = isNewRev };
    }

    #endregion

    #region 上传公用方法

    /// <summary>
    /// 获取单元格值
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>
    private string GetCellValue(ICell cell)
    {
        if (cell == null)
        {
            return "";
        }
        try
        {
            if (cell.CellType == CellType.Formula)//判断单元格是否有公式
            {
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    DateTime date = DateTime.FromOADate(cell.NumericCellValue);
                    if (date.Hour == 0)
                    {
                        return date.ToString("yyyy-MM-dd");
                    }
                    return date.ToString("yyyy-MM-dd HH:mm:ss");
                }
                cell.SetCellType(CellType.String);
            }
            if (cell.CellType == CellType.Numeric)
            {
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    DateTime date = DateTime.FromOADate(cell.NumericCellValue);
                    if (date.Hour == 0)
                    {
                        return date.ToString("yyyy-MM-dd");
                    }
                    return date.ToString("yyyy-MM-dd HH:mm:ss");
                }
                cell.SetCellType(CellType.String);
            }
        }
        catch
        {
            cell.SetCellType(CellType.String);
        }
        return cell.ToString().Trim();
    }

    /// <summary>
    /// 验证行数据是否有效（验证行数据每一列是否都为空）
    /// </summary>
    /// <param name="row"></param>
    /// <param name="isProduct"></param>
    /// <returns></returns>
    private bool IsValidRow(DataRow row, bool isProduct)
    {
        int c = 0;
        foreach (var item in row.ItemArray)
        {
            if (string.IsNullOrWhiteSpace(item.ToString()))
            {
                c++;
            }
        }
        if (isProduct && c + 1 == row.ItemArray.Length && row["Qty2Required"].ToString() == "0")
        {
            return false;
        }
        return c == row.ItemArray.Length ? false : true;
    }

    /// <summary>
    /// 获取有效重量数值
    /// </summary>
    /// <param name="qty2"></param>
    /// <returns></returns>
    private string GetProductWeight(string qty2)
    {
        try
        {
            for (int i = 0; i < qty2.Length; i++)
            {
                if ((int)qty2[i] > 127)
                {
                    qty2 = "0";
                    break;
                }
            }
            if (!string.IsNullOrWhiteSpace(qty2) && qty2 != "0")
            {
                qty2 = double.Parse(qty2).ToString("0.00");
            }
            else
            {
                qty2 = "0";
            }
            return qty2;
        }
        catch
        {
            return "0";
        }
    }

    /// <summary>
    /// 删除文件
    /// </summary>
    /// <param name="fpath">文件地址</param>
    private void DeleteFile(string fpath)
    {
        if (File.Exists(fpath))
        {
            new FileInfo(fpath).Attributes = FileAttributes.Normal;
            File.Delete(fpath);
        }
    }
    /// <summary>
    /// 没有文件路径，则创建
    /// </summary>
    /// <param name="context"></param>
    private void CreateDirectory(HttpContext context)
    {
        if (!Directory.Exists(context.Server.MapPath(fileBasePath)))
        {
            Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
        }
    }

    #endregion

}